﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text;
using System.Web.UI.WebControls;
using System.Web;
using System.Web.UI;

namespace Com.Becom.CommonLibrary.Office.Excel
{

    /// <summary>
    /// Microsoft Excel交互类。
    /// 须知：Excel不允许两个Sheet同名，即使Sheet1和sheet1也不行。
    /// </summary>
    public class CExcel
    {

        /// <summary>
        /// 列出Excel文件里所有Sheet的信息集合。
        /// </summary>
        /// <param name="strExcelFileName">Excel文件绝对路径和文件名。</param>
        /// <returns>Excel文件的Sheet结构集合。文件不存在时，返回null。</returns>
        public static DataTable ListSheets(string strExcelFileName)
        {
            // 判断文件是否存在。
            if (!File.Exists(strExcelFileName))
            {
                return null;
            }
            // 组合Excel连接串。
            string mystring = string.Format(@"Provider = Microsoft.Jet.OLEDB.4.0;Data Source = '{0}';Extended Properties='Excel 8.0;HDR=No;IMEX=1'", strExcelFileName);
            // 连接文件，填充读取，返回。
            OleDbConnection oleDbConnection = new OleDbConnection(mystring);
            oleDbConnection.Open();
            DataTable dataTable = oleDbConnection.GetSchema("Tables");
            oleDbConnection.Close();
            return dataTable;
        }

        /// <summary>
        /// 导入Excel文件的某个Sheet成DataSet数据集。
        /// </summary>
        /// <param name="strExcelFileName">Excel文件绝对路径和文件名。</param>
        /// <param name="SheetName">Sheet名称。兼容大小写，Sheet1与sHeET1视为同一个Sheet。自动删除左右空格和全部$。</param>
        /// <param name="FirstRowIsFieldName">Sheet的第一行是否视为字段名。建议用false。</param>
        /// <returns>该Sheet转换成的DataSet。文件不存在时，返回null。</returns>
        public static DataSet ImportFromFile(string strExcelFileName, string SheetName, bool FirstRowIsFieldName)
        {
            // 判断文件是否存在。
            if (!File.Exists(strExcelFileName))
            {
                return null;
            }
            // 首字段处理方式。
            string HDR = "No";
            if (FirstRowIsFieldName)
            {
                HDR = "Yes";
            }
            // 规范Sheet名称。
            SheetName = SheetName.Trim();
            SheetName = SheetName.Replace("$", "");
            // 组合Excel连接串。
            string mystring = string.Format(@"Provider = Microsoft.Jet.OLEDB.4.0;Data Source = '{0}';Extended Properties='Excel 8.0;HDR={1};IMEX=1'", strExcelFileName, HDR);
            // 连接文件，填充读取，返回。
            OleDbConnection oleDbConnection = new OleDbConnection(mystring);
            OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(@"select * from [" + SheetName + "$]", oleDbConnection);
            DataSet dataSet = new DataSet();
            oleDbDataAdapter.Fill(dataSet);
            return dataSet;
        }

        /// <summary>
        /// 导入Excel文件的某个Sheet成DataSet数据集。
        /// </summary>
        /// <param name="strExcelFileName">Excel文件绝对路径和文件名。</param>
        /// <param name="SheetIndex">Sheet索引位置。从0开始索引。不推荐这样使用，Excel里可能含有不可见的隐藏Sheet，这将导致序号混乱。</param>
        /// <param name="FirstRowIsFieldName">Sheet的第一行是否视为字段名。建议用false。</param>
        /// <returns>该Sheet转换成的DataSet。文件不存在时，返回null。</returns>
        public static DataSet ImportFromFile(string strExcelFileName, int SheetIndex, bool FirstRowIsFieldName)
        {
            // 分析Excel文件结构。
            DataTable dataTable = ListSheets(strExcelFileName);
            // 判断非空。
            if (null == dataTable)
            {
                return null;
            }
            // 提取相应的Sheet名。
            string SheetName = Convert.ToString(dataTable.Rows[SheetIndex]["TABLE_NAME"]);
            // 返回值。
            return ImportFromFile(strExcelFileName, SheetName, FirstRowIsFieldName);
        }

        /// <summary>
        /// 把DataSet导出成Excel文件。不必安装Excel软件。
        /// </summary>
        /// <param name="strExcelFileName">导出的Excel文件名称。</param>
        /// <param name="SheetName">Sheet名称。</param>
        /// <param name="dataSet">待导出的DataSet数据集。</param>
        /// <param name="FirstRowIsFieldName">是否把字段名导出成Excel文件第一行。</param>
        public static void ExportToFile(string strExcelFileName, string SheetName, DataSet dataSet, bool FirstRowIsFieldName, DataRow[] drColumn)
        {
            // 数据集不能为空。
            if (null == dataSet || dataSet.Tables.Count == 0)
            {
                return;
            }
            // 字符串缓冲区。
            StringBuilder sb = new StringBuilder();
            // 写入Excel文件头。
            sb.Append(@"<?xml version=""1.0"" encoding=""utf-8""?>
<?mso-application progid=""Excel.Sheet""?>
<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""
 xmlns:o=""urn:schemas-microsoft-com:office:office""
 xmlns:x=""urn:schemas-microsoft-com:office:excel""
 xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""
 xmlns:html=""http://www.w3.org/TR/REC-html40"">
 <DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office"">
  <Author></Author>
  <LastAuthor></LastAuthor>
  <Created></Created>
  <LastSaved></LastSaved>
  <Company></Company>
  <Version></Version>
 </DocumentProperties>
 <ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel"">
  <WindowHeight>8895</WindowHeight>
  <WindowWidth>11715</WindowWidth>
  <WindowTopX>240</WindowTopX>
  <WindowTopY>15</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID=""Default"" ss:Name=""Normal"">
   <Alignment ss:Vertical=""Center""/>
   <Borders/>
   <Font ss:FontName=""宋体"" x:CharSet=""134"" ss:Size=""12""/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID=""s21"">
   <NumberFormat ss:Format=""@""/>
  </Style>
 </Styles>
");
            // 写入Sheet名称。
            sb.Append(string.Format(@" <Worksheet ss:Name=""{0}"">
", SheetName));
            // 写入行列数。
            int ColsCount = dataSet.Tables[0].Columns.Count;
            int RowsCount = dataSet.Tables[0].Rows.Count;
            if (FirstRowIsFieldName)
            {
                RowsCount = RowsCount + 1;
            }
            sb.Append(string.Format(@"  <Table ss:ExpandedColumnCount=""{0}"" ss:ExpandedRowCount=""{1}"" x:FullColumns=""1""
   x:FullRows=""1"" ss:StyleID=""s21"" ss:DefaultColumnWidth=""54""
   ss:DefaultRowHeight=""14.25"">
", ColsCount, RowsCount));
            //设置列宽，从数据库取值
            if (drColumn != null)
            {
                int countTmp = Math.Min(ColsCount, drColumn.Length);
                for (int i = 0; i < countTmp; i++)
                {
                    string columnWidth = drColumn[i]["displaylen"].ToString();
                    sb.Append(string.Format(@"<Column ss:StyleID=""s21"" ss:Width=""{0}""/>", columnWidth));
                }
            }
            // 写入头一行。
            if (FirstRowIsFieldName)
            {
                // 写入行头。
                sb.Append(@"   <Row>
");
                // 遍历字段。
                foreach (DataColumn dataCoumn in dataSet.Tables[0].Columns)
                {
                    sb.Append(string.Format(@"    <Cell><Data ss:Type=""String"">{0}</Data></Cell>
", Convert.ToString(dataCoumn.ColumnName)));
                }
                // 写入行尾。
                sb.Append(@"   </Row>
");
            }
            // 遍历行列。
            foreach (DataRow dataRow in dataSet.Tables[0].Rows)
            {
                // 写入行头。
                sb.Append(@"   <Row>
");
                // 遍历字段。
                foreach (DataColumn dataCoumn in dataSet.Tables[0].Columns)
                {
                    // 写入列。
                    if (dataRow[dataCoumn.ColumnName] == DBNull.Value)
                    {
                        sb.Append(@"    <Cell><Data ss:Type=""String""></Data></Cell>
");
                    }
                    else
                    {
                        sb.Append(string.Format(@"    <Cell><Data ss:Type=""String"">{0}</Data></Cell>
", ConvertBool(Convert.ToString(dataRow[dataCoumn.ColumnName]))));
                    }
                }
                // 写入行尾。
                sb.Append(@"   </Row>
");
            }
            // 写入文件尾。
            sb.Append(@"  </Table>
  <WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">
   <Selected/>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>");
            // 写成文本文件。
            File.WriteAllText(strExcelFileName, sb.ToString());

        }

        static string ConvertBool(string text)
        {
            if (text.ToLower() == "true")
                return "是";
            else if (text.ToLower() == "false")
                return "否";
            else
                return text;
        }
    }
}
